﻿CREATE OR REPLACE FUNCTION trgfnupdate_project_currency()
	RETURNS trigger AS
	$BODY$
		DECLARE
			ctc_using_id integer;
			prj_using_id integer;
		Begin
			ctc_using_id =0;
			prj_using_id =0;
			if TG_OP='DELETE' then
				if old.contract_id>0 then
					ctc_using_id=old.contract_id;
				end if;
				if old.project_id>0 then
					prj_using_id = old.project_id;
				end if;
			elseif TG_OP='UPDATE' then
				if 
					((old.project_id is null and new.project_id>0) or
					(old.project_id >0 and new.project_id is null) or
					(new.project_id>0 or new.project_id<>old.project_id)) 
					and ((old.default_curr is not null or new.default_curr is not null) or
					(old.rate<>new.rate and (old.default_curr=True or new.default_curr=True)))
				then
					prj_using_id=new.project_id;
				end if;
				if 
					((old.contract_id is null and new.contract_id>0) or
					(old.contract_id >0 and new.contract_id is null) or
					(new.contract_id>0 or new.contract_id<>old.contract_id)) 
					and ((old.default_curr is not null or new.default_curr is not null) or
					(old.rate<>new.rate and (old.default_curr=True or new.default_curr=True)))
				then
					ctc_using_id=new.contract_id;
				end if;
			else
				if new.contract_id is not null then
					if new.default_curr=True and new.contract_id>0 then
						ctc_using_id = new.contract_id;
					end if;
				end if;
				if new.project_id is not null then
					if new.default_curr=True and new.project_id>0 then
						prj_using_id = new.project_id;
					end if;
				end if;
			end if;

			if ctc_using_id>0 then 
				Update
					kdvn_contract_client
				set
					contract_currency=(Select id from project_currency where default_curr=True and contract_id=ctc_using_id limit 1),
					contract_rate=Case when 
								(Select 
									name 
								from 
									project_currency 
								where default_curr=True and contract_id=ctc_using_id limit 1)='VND'
							Then 
								(Select 
									coalesce(rate,0)
								from 
									project_currency 
								where name='USD' and contract_id=ctc_using_id limit 1)
							else
								(Select 
									coalesce(rate,0)
								from 
									project_currency 
								where default_curr=True and contract_id=ctc_using_id limit 1) end
				where id = ctc_using_id;
			end if;
			
			if prj_using_id>0 then
				Update 
					kdvn_project 
				set 
					project_currency_reference=(Select id from project_currency where default_curr=True and project_id=prj_using_id limit 1),
					project_exrate_reference=
							Case when 
								(Select 
									name 
								from 
									project_currency 
								where default_curr=True and project_id=prj_using_id limit 1)='VND'
							Then 
								(Select 
									coalesce(rate,0)
								from 
									project_currency 
								where name='USD' and project_id=prj_using_id limit 1)
							else
								(Select 
									coalesce(rate,0)
								from 
									project_currency 
								where default_curr=True and project_id=prj_using_id limit 1)
							end
				where
					id = prj_using_id;
			end if;
		if TG_OP='DELETE' THEN
			Return old;
		ELSE
			RETURN NEW;
		END IF;
		end;    
	$BODY$
	LANGUAGE plpgsql VOLATILE
	COST 100;

-- DROP TRIGGER trgchange_project_currency on project_currency;
-- CREATE TRIGGER trgchange_project_currency
--   AFTER INSERT OR UPDATE or DELETE
--   ON project_currency
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfnupdate_project_currency();
